# @Time    : 2021/8/10 15:30
import os

import openpyxl


def list_of_groups(list_info, per_list_len):
    list_of_group = zip(*(iter(list_info),) * per_list_len)
    end_list = [list(i) for i in list_of_group]
    count = len(list_info) % per_list_len
    end_list.append(list_info[-count:]) if count != 0 else end_list
    return end_list


def create_excel(file_name, sheet_name, title_row, describe_cols=list()):
    """
    创建Excel表
    :param file_name: 表名
    :param sheet_name: 表单名
    :param title_row: 存放列名的列表
    :param describe_cols: 对各列所填信息的使用介绍
    :return:
    """
    if not os.path.exists(file_name):
        print('检测到当前目录下尚无配置文件，已为您建立名为%s的excel配置文件，请在其中填写好配置内容后再重启脚本' % file_name)
        workbook = openpyxl.Workbook()
        sheet = workbook.create_sheet(sheet_name, 0)
        for column in range(1, len(title_row) + 1):
            sheet.cell(row=1, column=column).value = title_row[column - 1]
        for describe in range(1, len(describe_cols) + 1):
            sheet.cell(row=2, column=describe).value = describe_cols[describe - 1]
        workbook.save(file_name)
        exit()


def load_excel_dict(file_name, begin_row=0):
    info_list = []
    workbook = openpyxl.load_workbook(filename=file_name)
    config_sheet = workbook.worksheets[0]
    keys = [key for index, key in enumerate(config_sheet.values) if index == 0][0]
    values = [value for index, value in enumerate(config_sheet.values) if index > begin_row and value[0] is not None]
    for values1 in values:
        dic = {}
        for key, value in zip(keys, values1):
            if key is not None:
                if value is not None:
                    dic[str(key).strip()] = str(value).strip()
                else:
                    dic[str(key).strip()] = value
        info_list.append(dic)
    print('从%s表中共读取到%s条数据' % (file_name, len(info_list)))
    return info_list


def write_excel(file_name, sheet_name, title_row, res_lists):
    """_summary_

    Args:
        file_name (String): _description_
        sheet_name (String): _description_
        title_row (List[String]): _description_
        res_lists (List[[String]]): _description_
    """    
    """
    将嵌套列表内的数据写入Excel文件
    :param file_name: 表名
    :param sheet_name: 表单名
    :param title_row: 存放列名的列表
    :param res_lists: 嵌套列表
    :return:
    """
    workbook = openpyxl.Workbook()
    workbook.remove(workbook.worksheets[0])
    new_sheet = workbook.create_sheet(sheet_name)
    for title_text, col_num in zip(title_row, range(1, len(title_row) + 1)):
        new_sheet.cell(row=1, column=col_num).value = title_text
    for single_row_data, row_num in zip(res_lists, range(2, len(res_lists) + 2)):
        for cell_content, col_num in zip(single_row_data, range(1, len(single_row_data) + 1)):
            new_sheet.cell(row=row_num, column=col_num).value = cell_content
    workbook.save(file_name)
    print("共导出%s条数据" % len(res_lists))
